<?php
// Authenticate
require("class.AuthenticationManager.php");
require("class.CommandMenu.php");
if (!$authenticationManager->isLoggedIn() || !$authenticationManager->hasClearance(CLEARANCE_ADMINISTRATOR)) {
    Header("Location: login.php?redirect=$_SERVER[PHP_SELF]&clearanceRequired=Administrator");
    exit;
}

// Connect to database:
$dbh = dbConnect();

// Include table_names
include("table_names.inc");

// Load local vars from superglobals:
if (isset($_REQUEST['day']))
{    
    $day = $_REQUEST['day'];
}
if (isset($_REQUEST['month']))
{    
    $month = $_REQUEST['month'];
}
if (isset($_REQUEST['year']))
{    
    $year = $_REQUEST['year'];
}
if (isset($_REQUEST['mode']))
{    
    $mode = $_REQUEST['mode'];
}
if (isset($_REQUEST['query']))
{    
    $query = $_REQUEST['query'];
}
if (isset($_REQUEST['contextUser']))
{    
    $contextUser = $_REQUEST['contextUser'];
}


//run the query  
list($qh,$num) = dbQuery($query);

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
//date_default_timezone_set('Europe/Paris');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();



// Insert Silkan Logo:
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('PHPExcel logo');
$objDrawing->setDescription('PHPExcel logo');
$objDrawing->setPath('./images/silkan.gif');
$objDrawing->setHeight(60);
$objDrawing->setCoordinates('B2');
//$objDrawing->setOffsetX(-10);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

// Merge Cells
$objPHPExcel->getActiveSheet()->mergeCells('B2:B4');

// Context User:

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('B6', 'User : ' .$contextUser. '');			
$objPHPExcel->getActiveSheet()->getStyle('B6')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('B6')->getFont()->setBold(true);

// Date:
$todayDate = date('D F j, Y',mktime(0,0,0,$month, $day, $year));
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('B8', 'Week Start : ' .$todayDate. ' ');			
$objPHPExcel->getActiveSheet()->getStyle('B8')->getFont()->setSize(14);

// Set document properties
$objPHPExcel->getProperties()->setCreator(       "TimeSheet.php")
							 ->setLastModifiedBy("TimeSheet.php")
							 ->setTitle(         "Office 2007 XLSX Test Document")
							 ->setSubject(       "Office 2007 XLSX Test Document")
							 ->setDescription(   "Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords(      "office 2007 openxml php")
							 ->setCategory(      "Test result file");



// Table:
$objPHPExcel->setActiveSheetIndex(0)               
            ->setCellValue('B10', 'Project / Task')
            ->setCellValue('c10', 'Task ID')
            ->setCellValue('d10', 'Mon')
            ->setCellValue('E10', 'Tue')
			->setCellValue('F10', 'Wed') 
			->setCellValue('G10', 'Thu') 
			->setCellValue('H10', 'Fri') 
			->setCellValue('I10', 'Total');
			
if ($num == 0) {
    // Add some data
    $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('B11', 'No data !');
	$objPHPExcel->getActiveSheet()->getStyle('B11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
    
	$objPHPExcel->getActiveSheet()->getStyle('C11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
	$objPHPExcel->getActiveSheet()->getStyle('D11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
    $objPHPExcel->getActiveSheet()->getStyle('E11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
	$objPHPExcel->getActiveSheet()->getStyle('F11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
    $objPHPExcel->getActiveSheet()->getStyle('G11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
	$objPHPExcel->getActiveSheet()->getStyle('H11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
    $objPHPExcel->getActiveSheet()->getStyle('I11')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0B0B') )));
} else {
    $row       = 11;
	$totalWeek = 0;
	
	while ($data = dbResult($qh)) 
	{
		$current_project_title = utf8_encode(stripslashes($data["projectTitle"]));
		$current_task_name     = utf8_encode(stripslashes($data["taskName"]));
		$identifier            = utf8_encode(stripslashes($data["identifier"]));
		$start_time            = $data["day_of_month"];
		$diff_sec              = $data["diff_sec"] / 60 / 60 / 8;
		$colum                 = 3 + ($data["day_of_month"] - $day ) ;
		$totalDay              = $diff_sec;
		$totalWeek             = $totalWeek + $diff_sec; 
		$exist                 = 0;
		$i                     = 11;

		for ($i == 11; $i<=$row; $i++)
		{
		    if( $identifier == $objPHPExcel->getActiveSheet()->getCell('C'.$i.'')->getValue()) 
		    {
			    $curentRow = $row;
				$row       = $i;
				$exist     = 1;
				//$value     = intval(objPHPExcel->getActiveSheet()->getCell('I'.$i.'')->getValue());
				//$totalDay  = $totalDay + $value;
			    break;
			
			} else {
			    // Add some data
    	        $objPHPExcel->setActiveSheetIndex(0)
		        			->setCellValueByColumnAndRow(1, $row,'' .$current_project_title. ' - ' .$current_task_name. '');
    		    $objPHPExcel->setActiveSheetIndex(0) 
                            ->setCellValueByColumnAndRow(2, $row, $identifier);	
			    break;
			
			
			}
		}
	    	
        //$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($colum, $row, $diff_sec);	
        $from = 'D'.$row.':H'.$row.'';		
		//$bob = =SUM('$from');
        //$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(8, $row, '=SUM(D$row:H$row)');
		
		$str = 'B'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		$str = 'C'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));			
		$str = 'D'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		$str = 'E'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));					
		$str = 'F'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		$str = 'G'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));			
		$str = 'H'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
		$str = 'I'.$row.'';
		$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    	    	array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));					
					
		if ($exist == 0)
        {		
		    $row ++;
        } else {
		    $row = $curentRow;
		}
		$exist = 0;
	}

				
	$objPHPExcel->setActiveSheetIndex(0)
			    ->setCellValueByColumnAndRow(1, $row,'Total');
	$objPHPExcel->setActiveSheetIndex(0)
			    ->setCellValueByColumnAndRow(8, $row, '' .$totalWeek. ' days');
				
	$str = 'B'.$row.'';
	$strToMerge = 'B'.$row.':H'.$row.'';
    $objPHPExcel->getActiveSheet()->mergeCells($strToMerge);
    $objPHPExcel->getActiveSheet()->getStyle($strToMerge)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
	$objPHPExcel->getActiveSheet()->getStyle($strToMerge)->getFont()->setSize(12);
    $objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle($str)->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
	
	// Total
	$str = 'I'.$row.'';
    $objPHPExcel->getActiveSheet()->getStyle($str)->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
	$objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setSize(12);
	$objPHPExcel->getActiveSheet()->getStyle($str)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				
				
				
				
				
}

// Signature:
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('B30', 'Signature :');			
$objPHPExcel->getActiveSheet()->getStyle('B30')->getFont()->setSize(12);

// Validation:
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('E30', 'Validation :');			
$objPHPExcel->getActiveSheet()->getStyle('E30')->getFont()->setSize(12);

		
		

		
//Column Dimension:
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(60);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);


// Set fonts			
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('B10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('c10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('c10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('c10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('c10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('d10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('d10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('d10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('d10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('E10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('E10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));	
			
$objPHPExcel->getActiveSheet()->getStyle('F10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('F10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('F10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('G10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('G10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('G10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('H10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('H10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('H10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('H10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));
			
$objPHPExcel->getActiveSheet()->getStyle('I10')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('I10')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('I10')->getBorders()->applyFromArray(
    		array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array( 'rgb' => '2B5714' ))));
$objPHPExcel->getActiveSheet()->getStyle('I10')->applyFromArray( 
            array( 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'BCCF00') )));			
			
			

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Weekly report');


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="WeeklyReport.xls"');
header('Cache-Control: max-age=0');

//$data = new Spreadsheet_Excel_Reader("WeeklyReport.xls",true,"UTF-16");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;






?>
